﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;

namespace IHISCETeim.DAL
{
    public class OQualityStrategyDAL
    {
        /// <summary>
        /// 新增对策基本信息
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static int Add(OQualityStrategyInfo obj,List<OQualityStrategyAttachmentInfo> listAtt)
        {
           
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    DbCommand dbCommand = DB.GetStoredProcCommand("OQualityStrategy_Add");
                    DB.AddInParameter(dbCommand, "RelevantEntryNum", DbType.String, obj.RelevantEntryNum);
                    DB.AddInParameter(dbCommand, "MacType", DbType.String, obj.MacType);
                    DB.AddInParameter(dbCommand, "ItemCode", DbType.String, obj.ItemCode);
                    DB.AddInParameter(dbCommand, "ItemName", DbType.String, obj.ItemName);
                    DB.AddInParameter(dbCommand, "ProblemClass", DbType.String, obj.ProblemClass);
                    DB.AddInParameter(dbCommand, "Frequency", DbType.String, obj.Frequency);
                    DB.AddInParameter(dbCommand, "Tiems", DbType.String, obj.Tiems);
                    DB.AddInParameter(dbCommand, "DuiceReport", DbType.String, obj.DuiceReport);
                    DB.AddInParameter(dbCommand, "FaultDescription", DbType.String, obj.FaultDescription);
                    DB.AddInParameter(dbCommand, "CauseAnalysis", DbType.String, obj.CauseAnalysis);
                    DB.AddInParameter(dbCommand, "LDC_Scheme", DbType.String, obj.LDC_Scheme);
                    DB.AddInParameter(dbCommand, "LDC_IsExport", DbType.String, obj.LDC_IsExport);
                    DB.AddInParameter(dbCommand, "LDC_ControlDate", DbType.String, obj.LDC_ControlDate);
                    DB.AddInParameter(dbCommand, "LDC_ControlSteel", DbType.String, obj.LDC_ControlSteel);
                    DB.AddInParameter(dbCommand, "LDC_ControlOther", DbType.String, obj.LDC_ControlOther);
                    DB.AddInParameter(dbCommand, "LDC_SendSvrBulletin", DbType.String, obj.LDC_SendSvrBulletin);
                    DB.AddInParameter(dbCommand, "LDC_HandleItemCode", DbType.String, obj.LDC_HandleItemCode);
                    DB.AddInParameter(dbCommand, "LDC_HandleItemName", DbType.String, obj.LDC_HandleItemName);
                    DB.AddInParameter(dbCommand, "YDC_Scheme", DbType.String, obj.YDC_Scheme);
                    DB.AddInParameter(dbCommand, "YDC_IsExport", DbType.String, obj.YDC_IsExport);
                    DB.AddInParameter(dbCommand, "YDC_ControlDate", DbType.String, obj.YDC_ControlDate);
                    DB.AddInParameter(dbCommand, "YDC_ControlSteel", DbType.String, obj.YDC_ControlSteel);
                    DB.AddInParameter(dbCommand, "YDC_ControlOther", DbType.String, obj.YDC_ControlOther);
                    DB.AddInParameter(dbCommand, "YDC_SendSvrBulletin", DbType.String, obj.YDC_SendSvrBulletin);
                    DB.AddInParameter(dbCommand, "YDC_HandleItemCode", DbType.String, obj.YDC_HandleItemCode);
                    DB.AddInParameter(dbCommand, "YDC_HandleItemName", DbType.String, obj.YDC_HandleItemName);

                    //return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
                    int qsid= Convert.ToInt32(DB.ExecuteScalar(dbCommand,trans));

                    #region 3.附件信息进行处理
                    // DbCommand dbCommanddeleteatt = DB.GetStoredProcCommand("OQualityStrategy_DeleteAttachmentByID", );
                    //DB.ExecuteScalar(dbCommanddeleteatt, trans);
                    foreach (OQualityStrategyAttachmentInfo itmAtt in listAtt)
                    {
                        DbCommand dbCommandAtt = DB.GetStoredProcCommand("OQualityStrategy_AddAttachment");
                        DB.AddInParameter(dbCommandAtt, "QSID", DbType.Int32, qsid);
                        DB.AddInParameter(dbCommandAtt, "AttType", DbType.String, itmAtt.AttType);
                        DB.AddInParameter(dbCommandAtt, "AttFileType", DbType.String, itmAtt.AttFileType);
                        DB.AddInParameter(dbCommandAtt, "AttPath", DbType.String, itmAtt.AttPath);
                        DB.AddInParameter(dbCommandAtt, "Remark", DbType.String, itmAtt.Remark);
                        DB.ExecuteScalar(dbCommandAtt, trans);
                    }
                    #endregion
                    trans.Commit();
                    return qsid;
                }
                catch (Exception exc)
                {
                    SEMailListDAL.AddAppError(string.Format("类方法:{0}<BR/> 错误:{1}", "OQualityStrategyDAL.Add", exc.Message));
                    //发生异常，事务回滚
                    trans.Rollback();
                    return -1;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 对策明细信息维护
        /// </summary>
        /// <param name="QSType">l 临时对策，y永久对策</param>
        /// <param name="obj"></param>
        /// <param name="listDetail"></param>
        /// <param name="listAtt"></param>
        /// <returns></returns>
        public static int UpdateDuiCe(string QSType, OQualityStrategyInfo obj, List<OQualityStrategyDetailInfo> listDetail, List<OQualityStrategyAttachmentInfo> listAtt)
        {

            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    #region 1.基本信息修改
                    DbCommand dbCommand = DB.GetStoredProcCommand("OQualityStrategy_Update");
                    DB.AddInParameter(dbCommand, "QSID", DbType.Int32, obj.QSID);
                    DB.AddInParameter(dbCommand, "RelevantEntryNum", DbType.String, obj.RelevantEntryNum);                    
                    DB.AddInParameter(dbCommand, "MacType", DbType.String, obj.MacType);
                    DB.AddInParameter(dbCommand, "ItemCode", DbType.String, obj.ItemCode);
                    DB.AddInParameter(dbCommand, "ItemName", DbType.String, obj.ItemName);
                    DB.AddInParameter(dbCommand, "ProblemClass", DbType.String, obj.ProblemClass);
                    DB.AddInParameter(dbCommand, "Frequency", DbType.String, obj.Frequency);
                    DB.AddInParameter(dbCommand, "Tiems", DbType.String, obj.Tiems);
                    DB.AddInParameter(dbCommand, "DuiceReport", DbType.String, obj.DuiceReport);
                    DB.AddInParameter(dbCommand, "FaultDescription", DbType.String, obj.FaultDescription);
                    DB.AddInParameter(dbCommand, "CauseAnalysis", DbType.String, obj.CauseAnalysis);
                    DB.AddInParameter(dbCommand, "LDC_Scheme", DbType.String, obj.LDC_Scheme);
                    DB.AddInParameter(dbCommand, "LDC_IsExport", DbType.String, obj.LDC_IsExport);
                    DB.AddInParameter(dbCommand, "LDC_ControlDate", DbType.String, obj.LDC_ControlDate);
                    DB.AddInParameter(dbCommand, "LDC_ControlSteel", DbType.String, obj.LDC_ControlSteel);
                    DB.AddInParameter(dbCommand, "LDC_ControlOther", DbType.String, obj.LDC_ControlOther);
                    DB.AddInParameter(dbCommand, "LDC_SendSvrBulletin", DbType.String, obj.LDC_SendSvrBulletin);
                    DB.AddInParameter(dbCommand, "LDC_HandleItemCode", DbType.String, obj.LDC_HandleItemCode);
                    DB.AddInParameter(dbCommand, "LDC_HandleItemName", DbType.String, obj.LDC_HandleItemName);
                    DB.AddInParameter(dbCommand, "YDC_Scheme", DbType.String, obj.YDC_Scheme);
                    DB.AddInParameter(dbCommand, "YDC_IsExport", DbType.String, obj.YDC_IsExport);
                    DB.AddInParameter(dbCommand, "YDC_ControlDate", DbType.String, obj.YDC_ControlDate);
                    DB.AddInParameter(dbCommand, "YDC_ControlSteel", DbType.String, obj.YDC_ControlSteel);
                    DB.AddInParameter(dbCommand, "YDC_ControlOther", DbType.String, obj.YDC_ControlOther);
                    DB.AddInParameter(dbCommand, "YDC_SendSvrBulletin", DbType.String, obj.YDC_SendSvrBulletin);
                    DB.AddInParameter(dbCommand, "YDC_HandleItemCode", DbType.String, obj.YDC_HandleItemCode);
                    DB.AddInParameter(dbCommand, "YDC_HandleItemName", DbType.String, obj.YDC_HandleItemName);
                    DB.ExecuteScalar(dbCommand,trans);
                    #endregion

                    #region 2.对策信息修改或添加
                    //2.1先删除相应对策所有的明细信息
                    DbCommand dbCommandDuiCe = DB.GetStoredProcCommand("OQualityStrategy_DeleteDetailByQSID", obj.QSID, QSType);
                    DB.ExecuteScalar(dbCommandDuiCe,trans);
                    //2.2 再添加相应的明细信息
                    foreach (OQualityStrategyDetailInfo itmDetil in listDetail)
                    {
                        DbCommand dbCommandDetil = DB.GetStoredProcCommand("OQualityStrategy_AddDetail");
                        DB.AddInParameter(dbCommandDetil, "QSID", DbType.Int32, obj.QSID);
                        DB.AddInParameter(dbCommandDetil, "QSType", DbType.String, QSType);
                        DB.AddInParameter(dbCommandDetil, "QSClass", DbType.String, itmDetil.QSClass);
                        DB.AddInParameter(dbCommandDetil, "PartSerNum", DbType.String, itmDetil.PartSerNum);
                        DB.AddInParameter(dbCommandDetil, "RelevantSteel1", DbType.String, itmDetil.RelevantSteel1);
                        DB.AddInParameter(dbCommandDetil, "RelevantSteel2", DbType.String, itmDetil.RelevantSteel2);
                        DB.AddInParameter(dbCommandDetil, "RelevantSteel3", DbType.String, itmDetil.RelevantSteel3);
                        DB.AddInParameter(dbCommandDetil, "StorageLocation", DbType.String, itmDetil.StorageLocation);
                        DB.AddInParameter(dbCommandDetil, "IsProgress", DbType.String, itmDetil.IsProgress);
                        DB.AddInParameter(dbCommandDetil, "ProgressDate", DbType.String, itmDetil.ProgressDate);
                        DB.AddInParameter(dbCommandDetil, "Remark", DbType.String, itmDetil.Remark);
                        DB.ExecuteScalar(dbCommandDetil, trans);
                    }
                    
                    #endregion

                    #region 3.附件信息进行处理
                   // DbCommand dbCommanddeleteatt = DB.GetStoredProcCommand("OQualityStrategy_DeleteAttachmentByID", );
                    //DB.ExecuteScalar(dbCommanddeleteatt, trans);
                    foreach (OQualityStrategyAttachmentInfo itmAtt in listAtt)
                    {
                        DbCommand dbCommanddeleteatt = DB.GetStoredProcCommand("OQualityStrategy_DeleteAttachmentByID", itmAtt.ID);
                        DB.ExecuteScalar(dbCommanddeleteatt, trans);

                        DbCommand dbCommandAtt = DB.GetStoredProcCommand("OQualityStrategy_AddAttachment");
                        DB.AddInParameter(dbCommandAtt, "QSID", DbType.Int32, obj.QSID);
                        DB.AddInParameter(dbCommandAtt, "AttType", DbType.String, itmAtt.AttType);
                        DB.AddInParameter(dbCommandAtt, "AttFileType", DbType.String, itmAtt.AttFileType);
                        DB.AddInParameter(dbCommandAtt, "AttPath", DbType.String, itmAtt.AttPath);
                        DB.AddInParameter(dbCommandAtt, "Remark", DbType.String, itmAtt.Remark);
                        DB.ExecuteScalar(dbCommandAtt, trans);
                    }
                    #endregion
                    trans.Commit();
                    return 1;
                }
                catch (Exception exc)
                {
                    SEMailListDAL.AddAppError(string.Format("类方法:{0}<BR/> 错误:{1}", "OQualityStrategyDAL.UpdateDuiCe", exc.Message));
                    //发生异常，事务回滚
                    trans.Rollback();
                    return -1;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
           
        }
        /// <summary>
       /// 删除对策信息，将删除所有的包括明细及附件
       /// </summary>
       /// <param name="QSID"></param>
       /// <returns></returns>
        public static bool Delete(int QSID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OQualityStrategy_Delete", QSID);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static IDataReader GetByID(int QSID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OQualityStrategy_GetByID", QSID);
            return DB.ExecuteReader(dbCommand);
        }
      
        public static IDataReader GetListAttachmentByQSID(int QSID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OQualityStrategy_GetListAttachmentByQSID", QSID);
            return DB.ExecuteReader(dbCommand);
        }
        public static IDataReader GetListDetailByQSID(int ID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OQualityStrategy_GetListDetailByQSID", ID);
            return DB.ExecuteReader(dbCommand);
        }
        public static DataTable GetPagination(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OQualityStrategy_GetPagination");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTable dt = DB.ExecuteDataSet(dbCommand).Tables[0];

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }
        /// <summary>
        /// 修改基本信息
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="listAtt"></param>
        /// <returns></returns>
        public static bool Update(OQualityStrategyInfo obj, List<OQualityStrategyAttachmentInfo> listAtt)
        {
          
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    DbCommand dbCommand = DB.GetStoredProcCommand("OQualityStrategy_Update");
                    DB.AddInParameter(dbCommand, "QSID", DbType.Int32, obj.QSID);
                    DB.AddInParameter(dbCommand, "RelevantEntryNum", DbType.String, obj.RelevantEntryNum);
                    DB.AddInParameter(dbCommand, "MacType", DbType.String, obj.MacType);
                    DB.AddInParameter(dbCommand, "ItemCode", DbType.String, obj.ItemCode);
                    DB.AddInParameter(dbCommand, "ItemName", DbType.String, obj.ItemName);
                    DB.AddInParameter(dbCommand, "ProblemClass", DbType.String, obj.ProblemClass);
                    DB.AddInParameter(dbCommand, "Frequency", DbType.String, obj.Frequency);
                    DB.AddInParameter(dbCommand, "Tiems", DbType.String, obj.Tiems);
                    DB.AddInParameter(dbCommand, "DuiceReport", DbType.String, obj.DuiceReport);
                    DB.AddInParameter(dbCommand, "FaultDescription", DbType.String, obj.FaultDescription);
                    DB.AddInParameter(dbCommand, "CauseAnalysis", DbType.String, obj.CauseAnalysis);
                    DB.AddInParameter(dbCommand, "LDC_Scheme", DbType.String, obj.LDC_Scheme);
                    DB.AddInParameter(dbCommand, "LDC_IsExport", DbType.String, obj.LDC_IsExport);
                    DB.AddInParameter(dbCommand, "LDC_ControlDate", DbType.String, obj.LDC_ControlDate);
                    DB.AddInParameter(dbCommand, "LDC_ControlSteel", DbType.String, obj.LDC_ControlSteel);
                    DB.AddInParameter(dbCommand, "LDC_ControlOther", DbType.String, obj.LDC_ControlOther);
                    DB.AddInParameter(dbCommand, "LDC_SendSvrBulletin", DbType.String, obj.LDC_SendSvrBulletin);
                    DB.AddInParameter(dbCommand, "LDC_HandleItemCode", DbType.String, obj.LDC_HandleItemCode);
                    DB.AddInParameter(dbCommand, "LDC_HandleItemName", DbType.String, obj.LDC_HandleItemName);
                    DB.AddInParameter(dbCommand, "YDC_Scheme", DbType.String, obj.YDC_Scheme);
                    DB.AddInParameter(dbCommand, "YDC_IsExport", DbType.String, obj.YDC_IsExport);
                    DB.AddInParameter(dbCommand, "YDC_ControlDate", DbType.String, obj.YDC_ControlDate);
                    DB.AddInParameter(dbCommand, "YDC_ControlSteel", DbType.String, obj.YDC_ControlSteel);
                    DB.AddInParameter(dbCommand, "YDC_ControlOther", DbType.String, obj.YDC_ControlOther);
                    DB.AddInParameter(dbCommand, "YDC_SendSvrBulletin", DbType.String, obj.YDC_SendSvrBulletin);
                    DB.AddInParameter(dbCommand, "YDC_HandleItemCode", DbType.String, obj.YDC_HandleItemCode);
                    DB.AddInParameter(dbCommand, "YDC_HandleItemName", DbType.String, obj.YDC_HandleItemName);

                    DB.ExecuteScalar(dbCommand, trans);

                    #region 3.附件信息进行处理
                    // DbCommand dbCommanddeleteatt = DB.GetStoredProcCommand("OQualityStrategy_DeleteAttachmentByID", );
                    //DB.ExecuteScalar(dbCommanddeleteatt, trans);
                    foreach (OQualityStrategyAttachmentInfo itmAtt in listAtt)
                    {
                        DbCommand dbCommanddeleteatt = DB.GetStoredProcCommand("OQualityStrategy_DeleteAttachmentByID", itmAtt.ID);
                        DB.ExecuteScalar(dbCommanddeleteatt, trans);


                        DbCommand dbCommandAtt = DB.GetStoredProcCommand("OQualityStrategy_AddAttachment");
                        DB.AddInParameter(dbCommandAtt, "QSID", DbType.Int32, obj.QSID);
                        DB.AddInParameter(dbCommandAtt, "AttType", DbType.String, itmAtt.AttType);
                        DB.AddInParameter(dbCommandAtt, "AttFileType", DbType.String, itmAtt.AttFileType);
                        DB.AddInParameter(dbCommandAtt, "AttPath", DbType.String, itmAtt.AttPath);
                        DB.AddInParameter(dbCommandAtt, "Remark", DbType.String, itmAtt.Remark);
                        DB.ExecuteScalar(dbCommandAtt, trans);
                    }
                    #endregion
                    trans.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    SEMailListDAL.AddAppError(string.Format("类方法:{0}<BR/> 错误:{1}", "OQualityStrategyDAL.Update", exc.Message));
                    //发生异常，事务回滚
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }
    }
}
